package database;

import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;

import org.apache.commons.collections.FastHashMap;

public class ConnectionDatabase {
	private String TABLE_NAME = "public.\"MatrixPAP\"";
	private String TABLE_NAME_PARAMETERS = "public.\"MatrixPAP\"(id, \"array\")";
	private Connection c;
	private Statement stmt;

	public ConnectionDatabase() {
		try {
			c = getConexao();
			stmt = c.createStatement();
			deleteAllInDB();
		} catch (SQLException e) {
			System.err.println("Falha ao criar statement.");
			e.printStackTrace();
		}
	}

	public Connection getConexao() {
		try {
			Class.forName("org.postgresql.Driver");
			Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/PAP", "postgres", "t2002b");
			System.out.println("Conectado com sucesso.");
			return con;
		} catch (Exception e) {
			System.out.println("Falha ao conectar.");
			throw new RuntimeException();
		}
	}

	public static void main(String[] args) throws SQLException {
		ConnectionDatabase cd = new ConnectionDatabase();

		ArrayList<Float> list = new ArrayList<Float>();
		list.add(1.0f);
		list.add(50.980f);

		// cd.insertInDB(1, list);

		// cd.selectAll();
		cd.updateInDB(1, list);
		System.out.println(cd.selectForID(1));
	}

	public ArrayList<Float> selectForID(Integer idObj) {
		try {
			c.setAutoCommit(false);

			ResultSet rs = stmt.executeQuery("SELECT * FROM " + TABLE_NAME + " WHERE id = " + idObj);
			rs.next();
			Array array = rs.getArray("array");
			ArrayList<Float> list = new ArrayList<Float>(Arrays.asList((Float[])array.getArray()));
			rs.close();
			
			return list;
		} catch (Exception e) {
			System.err.println("Operacao select nao concluida.");
			e.printStackTrace();
		}
		return null;
	}
	
	public FastHashMap selectLotOf(int maxSize, int indexAtual, int indexCantRemove) {
		FastHashMap matrixCohesivenessCoupling = new FastHashMap();
		try {
			c.setAutoCommit(false);
			if (indexAtual == 51) {
				System.out.println();
			}
			
			String textQuery = "";
			int top = indexAtual + maxSize;
			if (indexAtual != indexCantRemove) {
				top--;
				textQuery = "SELECT * FROM " + TABLE_NAME + " WHERE id = " + indexAtual + " OR id = " + indexCantRemove;
			} else {
				textQuery = "SELECT * FROM " + TABLE_NAME + " WHERE id = " + indexAtual;
			}
			
			//Para o caso do indiceAtual nao ser o que nao pode ser removido (ex: no for aninhado o indice nao removido eh
			// o indice do primeiro for. Os indices do segundo for ficam fazendo swap. O top-- eh para garantir o tamanho max da matriz.
			
			for (int i = ++indexAtual; i < top; i++) {
				textQuery += " OR id = " + i;
			}
			ResultSet rs = stmt.executeQuery(textQuery);
			while (rs.next()) {
				int id = rs.getInt("id");
				Array array = rs.getArray("array");
				ArrayList<Float> list = new ArrayList<Float>(Arrays.asList((Float[])array.getArray()));
				matrixCohesivenessCoupling.put(id, list);
			}
			rs.close();
			
		} catch (Exception e) {
			System.err.println("Operacao select nao concluida.");
			e.printStackTrace();
		}
		
		return matrixCohesivenessCoupling;
	}

	public void selectAll() {
		try {
			c.setAutoCommit(false);

			ResultSet rs = stmt.executeQuery("SELECT * FROM " + TABLE_NAME);
			while (rs.next()) {
				int id = rs.getInt("id");
				Array array = rs.getArray("array");
				System.out.println("ID = " + id);
				System.out.println("ARRAY[0] = " + ((Float[]) array.getArray())[0]);
				System.out.println();
			}
			rs.close();
		} catch (Exception e) {
			System.err.println("Operacao select nao concluida.");
			e.printStackTrace();
		}
	}

	public void insertInDB(Integer id, ArrayList<Float> array) {
		String arrayQuery = fortmatArrayToQuery(array);
		String query = "INSERT INTO " + TABLE_NAME_PARAMETERS + " VALUES (" + id + ", '" + arrayQuery + "')";
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			System.err.println("Erro ao inserir array na matriz.");
			e.printStackTrace();
		}
	}
	
	public void insertALotInDB(FastHashMap hashMap) {
		String query = "";
		for (Object key : hashMap.keySet()) {
			query += "INSERT INTO " + TABLE_NAME_PARAMETERS + " VALUES (" + key + ", '" + fortmatArrayToQuery((ArrayList<Float>) hashMap.get(key)) + "');\n";
		}
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			System.err.println("Erro ao inserir array na matriz.");
			e.printStackTrace();
		}
	}

	private static String fortmatArrayToQuery(ArrayList<Float> array) {
		String result = array.toString();
		result = result.replace("[", "{");
		result = result.replace("]", "}");
		return result;
	}
	
	public void updateInDB(Integer id, ArrayList<Float> array) {
		String arrayQuery = fortmatArrayToQuery(array);
		String query = "UPDATE " + TABLE_NAME + " SET \"array\" = '" + arrayQuery + "' WHERE id = " + id;
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			System.err.println("Erro ao atualizar array na matriz.");
			e.printStackTrace();
		}
	}
	
	public void deleteAllInDB() {
		String query = "DELETE FROM " + TABLE_NAME;
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			System.err.println("Erro ao limpar o banco.");
			e.printStackTrace();
		}
	}

	public void close() {
		try {
			stmt.close();
			c.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
